package dao;

import bean.DormAdmin;
import bean.Student;
import util.DriverManegerUitl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class AdminDao {
    private Connection conn;
    private PreparedStatement pre;
    private ResultSet resultSet;
    //宿管通过用户名密码登录

    /**
     * 宿管通过用户名密码登录
     * @param dormAdmin
     * @return
     */
    public DormAdmin Login(DormAdmin dormAdmin){
        conn= DriverManegerUitl.getConnection();
        try {
            pre=conn.prepareStatement("select * from dormadmin where username=? and password=?");
            pre.setString(1,dormAdmin.getUsername());
            pre.setString(2,dormAdmin.getPassword());
            resultSet= pre.executeQuery();
            while (resultSet.next()){
            dormAdmin.setDormadminid(resultSet.getString("dormadminid"));
            dormAdmin.setUsername(resultSet.getString("username"));
            dormAdmin.setPassword(resultSet.getString("password"));
            dormAdmin.setDormadminname(resultSet.getString("dormadminname"));
            }
            return dormAdmin;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DriverManegerUitl.closed(conn,pre,resultSet);
        }
        return null;
    }

    //返回学生所有信息
    public ArrayList<Student> getStudentInfo(){
        conn=DriverManegerUitl.getConnection();
        ArrayList<Student> list=new ArrayList<>();
        try {
            pre=conn.prepareStatement("select * from student");
            resultSet= pre.executeQuery();
            while (resultSet.next()){
                Student stu=new Student();
                stu.setStudentid(resultSet.getString("studentid"));
                stu.setStudentname(resultSet.getString("studentname"));
                stu.setMajor(resultSet.getString("major"));
                stu.setGender(resultSet.getString("gender"));
                stu.setDepartment(resultSet.getString("department"));
                stu.setClasses(resultSet.getString("classes"));
                stu.setDormitoryid(resultSet.getString("dormitoryid"));
                stu.setPhoneid(resultSet.getString("phoneid"));
                stu.setEntrytime(resultSet.getDate("entrytime"));
                stu.setUsername(resultSet.getString("username"));
                stu.setPassword(resultSet.getString("password"));
                list.add(stu);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DriverManegerUitl.closed(conn,pre,resultSet);
        }
        return null;
    }

    /**
     * 模糊查询
     * @param stu
     * @return
     */
//根据不同条件查询学生信息 字段查询     名字有模糊查询
    public ArrayList<Student> getStudentInfoByCondition(Student stu){
        conn=DriverManegerUitl.getConnection();
        try {
            String sql="select * from student where 1 = 1";
            ArrayList<String> count=new ArrayList<>();//计数添加了几个查询语句
            ArrayList<Student> stuList=new ArrayList<>();
            String studentid = stu.getStudentid();
            String studentname = stu.getStudentname();
            String dormitoryid = stu.getDormitoryid();
            String major = stu.getMajor();
            String department = stu.getDepartment();
            String classes = stu.getClasses();
            String phoneid = stu.getPhoneid();

            if (studentid!=""){
                sql+=" and studentid=?";
                count.add(studentid);
            }
            if (studentname!=""){
                sql+=" and studentname like ?";
                count.add("%"+studentname+"%");
            }
            if (dormitoryid!=""){
                sql+=" and dormitoryid=?";
                count.add(dormitoryid);
            }
            if (major!=""){
                sql+=" and major=?";
                count.add(major);
            }
            if (department!=""){
                sql+=" and department=?";
                count.add(department);
            }
            if (classes!=""){
                sql+=" and classes=?";
                count.add(classes);
            }
            if (phoneid!=""){
                sql+=" and phoneid=?";
                count.add(phoneid);
            }

            pre=conn.prepareStatement(sql);
//            select * from where 1=1 and ****=? and ....
//            循环输出存放在count列表里的值
            for (int i=0;i<count.size();i++){
                pre.setString(i+1,count.get(i));
            }

            resultSet= pre.executeQuery();

            while (resultSet.next()){
                Student student=new Student();

                student.setStudentid(resultSet.getString("studentid"));
                student.setStudentname(resultSet.getString("studentname"));
                student.setGender(resultSet.getString("gender"));
                student.setMajor(resultSet.getString("major"));
                student.setDepartment(resultSet.getString("department"));
                student.setClasses(resultSet.getString("classes"));
                student.setDormitoryid(resultSet.getString("dormitoryid"));
                student.setPhoneid(resultSet.getString("phoneid"));
                student.setEntrytime(resultSet.getDate("entrytime"));
                student.setUsername(resultSet.getString("username"));
                student.setPassword(resultSet.getString("password"));
                stuList.add(student);
            }
            return stuList;

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DriverManegerUitl.closed(conn,pre,resultSet);
        }
        return null;
    }
}
